IF  EXISTS (select * from dbo.sysobjects where id = object_id(N'[dbo].[PreservationNoticeHistoryGetByNewSitePaged]') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE [dbo].[PreservationNoticeHistoryGetByNewSitePaged]
GO

CREATE PROCEDURE [dbo].[PreservationNoticeHistoryGetByNewSitePaged] 
(
    @NewSiteID uniqueidentifier,
    @PageSize int, 
    @CurrentPage int,
    @ItemCount int output
)
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;
 
    DECLARE @UpperBand int, @LowerBand int
 
            -- Get the total count of rows
            SET @ItemCount = (SELECT COUNT(*) 
							  FROM PreservationNoticeHistory
							  WHERE NewSiteID = @NewSiteID)
 
            -- Calculate @UpperBand & @LowerBand
            SET @LowerBand = (@CurrentPage - 1) * @PageSize
            SET @UpperBand = (@CurrentPage * @PageSize) + 1
 
            -- Create a temporary table
            CREATE TABLE #AllRows(
                        RowID int PRIMARY KEY CLUSTERED IDENTITY(1,1)
						,[PreservationNoticeHistoryID] [int]
						,[NewSiteID] [uniqueidentifier]
						,[PreservationNoticeID] [int]
						,[EventType] [varchar](50)
						,[PreservationNoticeName] [varchar](100)
						,[DateEmailMessageCreated] [datetime]
						,[DateEmailMessageSent] [datetime]
						,DateAcknowledged datetime
						,[FromAddress] [varchar](500)
						,[CustodianEmail] [varchar](500)
						,[MessageSubject] [varchar](500)
						,[MessageBody] [text]
						,[Identifier] [varchar](500)
						,[InterviewDate] [datetime]
						,[CustodianID] [int]
						,[CustodianLastName] [varchar](200)
						,[CustodianFirstName] [varchar](200)
						,[HasAttachments] [bit]
						,[VerificationTries] [int]
            )
 
            -- Insert all the rows
            INSERT INTO #AllRows
                        SELECT [PreservationNoticeHistoryID]
						,[NewSiteID]
						,[PreservationNoticeID]
						,[EventType]
						,[PreservationNoticeName]
						,[DateEmailMessageCreated]
						,[DateEmailMessageSent]
						,DateAcknowledged
						,[FromAddress]
						,[CustodianEmail]
						,[MessageSubject]
						,[MessageBody]
						,[Identifier]
						,[InterviewDate]
						,[CustodianID]
						,[CustodianLastName]
						,[CustodianFirstName]
						,[HasAttachments]
						,[VerificationTries]
                        FROM [PreservationNoticeHistory]
                        WHERE NewSiteID = @NewSiteID
                        ORDER BY PreservationNoticeHistoryID DESC
 
            -- Select and return desired paged rows
                        SELECT RowID
						,[PreservationNoticeHistoryID]
						,[NewSiteID]
						,[PreservationNoticeID]
						,[EventType]
						,[PreservationNoticeName]
						,[DateEmailMessageCreated]
						,[DateEmailMessageSent]
						,DateAcknowledged
						,[FromAddress]
						,[CustodianEmail]
						,[MessageSubject]
						,[MessageBody]
						,[Identifier]
						,[InterviewDate]
						,[CustodianID]
						,[CustodianLastName]
						,[CustodianFirstName]
						,[HasAttachments]
						,[VerificationTries]                        
						FROM #AllRows
                        WHERE RowID > @LowerBand AND RowID < @UpperBand
 
END
